﻿Imports System.Data.SqlClient
Imports DTO
Public Class MachDieuKhienDAO
    Function themMachDK(ByVal _tenMach As String, ByVal _maLoai As Integer, ByVal _hangSX As String,
                        ByVal _soCongPin As Integer, ByVal _dongDien As Double, ByVal _HSTangThem As Double, ByVal _gia As Integer) As Integer
        Dim sql As String = "insert into MACHDIEUKHIEN(TenMach, MaLoai, HangSanXuat, SoCongPin, DongDien, HieuSuatTangThem, Gia)" &
            " values(@TenMach, @MaLoai, @HangSanXuat, @SoCongPin, @DongDien, @HieuSuatTangThem, @Gia)"
        Dim conn = DataProvider.getConnection()
        Dim command As New SqlCommand(sql, conn)

        command.Parameters.AddWithValue("@TenMach", _tenMach)
        command.Parameters.AddWithValue("@MaLoai", _maLoai)
        command.Parameters.AddWithValue("@HangSanXuat", _hangSX)
        command.Parameters.AddWithValue("@SoCongPin", _soCongPin)
        command.Parameters.AddWithValue("@DongDien", _dongDien)
        command.Parameters.AddWithValue("@HieuSuatTangThem", _HSTangThem)
        command.Parameters.AddWithValue("@Gia", _gia)

        Try
            conn.Open()
            Dim rowAffect = command.ExecuteNonQuery()
            Return rowAffect
        Catch ex As Exception
            Throw New Exception("DAO: insert into MachDieuKhien error - themMachDK", ex)
        Finally
            conn.Close()
        End Try

        Return 0
    End Function

    Function layMaMachLonNhat() As Integer
        Dim sql As String = "select max(MaMach) as maxid from MACHDIEUKHIEN"
        Dim conn As SqlConnection = DataProvider.getConnection()
        Dim command As New SqlCommand(sql, conn)
        conn.Open()
        Try
            Dim reader As SqlDataReader = command.ExecuteReader()
            If reader.Read() Then
                Return reader("maxid")
            End If
        Catch
        Finally
            conn.Close()
        End Try

        Return -1
    End Function

    Function layDSMachDK() As List(Of MachDieuKhienDTO)
        Dim ret As New List(Of MachDieuKhienDTO)
        Dim sql As String = "select * from MACHDIEUKHIEN"
        Dim conn As SqlConnection = DataProvider.getConnection()
        Dim command As New SqlCommand(sql, conn)
        conn.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()
        Dim mdk As MachDieuKhienDTO = Nothing
        While reader.Read()
            mdk = New MachDieuKhienDTO
            mdk.MaMach = reader("MaMach")
            mdk.TenMach = reader("TenMach")
            mdk.MaLoai = DirectCast(reader("MaLoai"), Integer)
            mdk.HangSanXuat = reader("HangSanXuat")
            mdk.SoCongPin = DirectCast(reader("SoCongPin"), Integer)
            mdk.DongDien = DirectCast(reader("DongDien"), Double)
            mdk.Gia = DirectCast(reader("Gia"), Integer)
            mdk.HieuSuatTangThem = DirectCast(reader("HieuSuatTangThem"), Double)

            ret.Add(mdk)
        End While
        conn.Close()
        Return ret
    End Function

    Function capNhatMachDK(ByVal maMach As Integer, ByVal tenMach As String, ByVal maLoai As Integer,
                           ByVal hangSX As String, ByVal soCongPin As Integer, ByVal hsTangThem As Integer,
                           ByVal dongDien As Integer, ByVal gia As Integer) As Integer
        Dim ret As Integer
        Dim sql As String = "update MACHDIEUKHIEN set TenMach=N'" & tenMach & "', MaLoai= " & maLoai &
            ", HangSanXuat=N'" & hangSX & "', SoCongPin=" & soCongPin & ", HieuSuatTangThem=" & hsTangThem &
            ", dongDien = " & dongDien & ", Gia =" & gia & " where MaMach=" & maMach

        Dim conn As SqlConnection = DataProvider.getConnection()
        Dim command As New SqlCommand(sql, conn)
        conn.Open()
        ret = command.ExecuteNonQuery()
        conn.Close()
        Return ret
    End Function

    Function xoaMachDK(ByVal maMach As Integer) As Integer
        Dim ret As Integer
        Dim sql As String = "delete from MACHDIEUKHIEN where MaMach=" & maMach

        Dim conn As SqlConnection = DataProvider.getConnection()
        Dim command As New SqlCommand(sql, conn)
        conn.Open()
        ret = command.ExecuteNonQuery()
        conn.Close()
        Return ret
    End Function

    Function layDSMachDKs(ByVal ids As List(Of Integer)) As List(Of MachDieuKhienDTO)
        Dim ret As New List(Of MachDieuKhienDTO)
        If (ids.Count = 0) Then
            Return ret
        End If
        Dim sql As String = "select * from MACHDIEUKHIEN where "

        For Each id As Integer In ids
            sql = sql & " MaMach=" & id & " or"
        Next

        sql = sql.Substring(0, sql.Length - 2)

        Dim conn As SqlConnection = DataProvider.getConnection()
        Dim command As New SqlCommand(sql, conn)
        conn.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()
        Dim mdk As MachDieuKhienDTO = Nothing
        While reader.Read()
            mdk = New MachDieuKhienDTO
            mdk.MaMach = reader("MaMach")
            mdk.TenMach = reader("TenMach")
            mdk.MaLoai = DirectCast(reader("MaLoai"), Integer)
            mdk.HangSanXuat = reader("HangSanXuat")
            mdk.SoCongPin = DirectCast(reader("SoCongPin"), Integer)
            mdk.DongDien = DirectCast(reader("DongDien"), Double)
            mdk.Gia = DirectCast(reader("Gia"), Integer)
            mdk.HieuSuatTangThem = DirectCast(reader("HieuSuatTangThem"), Double)

            ret.Add(mdk)
        End While
        conn.Close()
        Return ret
    End Function

    Function layMachDK(ByVal id As Integer) As MachDieuKhienDTO

        Dim sql As String = "select * from MACHDIEUKHIEN where MaMach=" & id

        Dim conn As SqlConnection = DataProvider.getConnection()
        Dim command As New SqlCommand(sql, conn)
        conn.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()
        Dim mdk As MachDieuKhienDTO = Nothing
        While reader.Read()
            mdk = New MachDieuKhienDTO
            mdk.MaMach = reader("MaMach")
            mdk.TenMach = reader("TenMach")
            mdk.MaLoai = DirectCast(reader("MaLoai"), Integer)
            mdk.HangSanXuat = reader("HangSanXuat")
            mdk.SoCongPin = DirectCast(reader("SoCongPin"), Integer)
            mdk.DongDien = DirectCast(reader("DongDien"), Double)
            mdk.Gia = DirectCast(reader("Gia"), Integer)
            mdk.HieuSuatTangThem = DirectCast(reader("HieuSuatTangThem"), Double)
        End While
        conn.Close()
        Return mdk
    End Function

    Function timKiemNC(ByVal tenMach As String, ByVal maLoai As Object, ByVal hangSX As String) As List(Of MachDieuKhienDTO)
        Dim ret As New List(Of MachDieuKhienDTO)

        Dim sql As String = "select * from MACHDIEUKHIEN where "
        If (tenMach.Length > 0) Then
            sql = sql & " TenMach like '%" & tenMach & "%' and"
        End If

        If (Not DirectCast(maLoai, Integer) = 0) Then
            sql = sql & " MaLoai = " & maLoai & " and"
        End If

        If (hangSX.Length > 0) Then
            sql = sql & " HangSanXuat like '%" & hangSX & "%' and"
        End If

        sql = sql.Substring(0, sql.Length - 3)

        Dim conn As SqlConnection = DataProvider.getConnection()
        Dim command As New SqlCommand(sql, conn)
        conn.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()
        Dim mdk As MachDieuKhienDTO = Nothing
        While reader.Read()
            mdk = New MachDieuKhienDTO
            mdk.MaMach = reader("MaMach")
            mdk.TenMach = reader("TenMach")
            mdk.MaLoai = DirectCast(reader("MaLoai"), Integer)
            mdk.HangSanXuat = reader("HangSanXuat")
            mdk.SoCongPin = DirectCast(reader("SoCongPin"), Integer)
            mdk.DongDien = DirectCast(reader("DongDien"), Double)
            mdk.Gia = DirectCast(reader("Gia"), Integer)
            mdk.HieuSuatTangThem = DirectCast(reader("HieuSuatTangThem"), Double)

            ret.Add(mdk)
        End While
        conn.Close()
        Return ret
    End Function

End Class
